﻿CREATE PROCEDURE [dbo].[ListUserTimeSummary]
@ManagerUserID INT, @UserIDList NVARCHAR (512), @StartDate DATETIME, @EndDate DATETIME
AS
DECLARE 
    @sSqlString nvarchar(1024),
    @sSubSql nvarchar(1024),
    @RoleID int
    
SELECT @RoleID = RoleID
FROM Users 
WHERE UserID = @ManagerUserID;

IF (@RoleID = 1)
    BEGIN
    SET @sSqlString = 'SELECT Sum(EL.Duration) TotalHours, U.UserID, U.UserName'
    SET @sSqlString = @sSqlString + ' FROM EntryLog EL Inner Join Users U On EL.UserID = U.UserID WHERE U.UserID IN (' + @UserIDList + ')'
    SET @sSqlString = @sSqlString + ' and EL.EntryDate >= @1 and EL.EntryDate <= @2  GROUP BY U.UserID, U.UserName'
    END
ELSE IF (@RoleID = 2)
    BEGIN   
    SET @sSubSql = 'SELECT PM.UserID FROM Projects P INNER JOIN ProjectMembers PM'
    SET @sSubSql = @sSubSql + ' ON P.ProjectID = PM.ProjectID WHERE P.ManagerUserID = @3 AND PM.UserID IN (' + @UserIDList + ')' 
    
    SET @sSqlString = 'SELECT Sum(EL.Duration) TotalHours, U.UserID, U.UserName'
    SET @sSqlString = @sSqlString + ' FROM EntryLog EL Inner Join Users U On EL.UserID = U.UserID WHERE U.UserID IN (' + @sSubSql + ')'
    SET @sSqlString = @sSqlString + ' AND EL.ProjectID IN (SELECT ProjectID From Projects Where ManagerUserID = @3) '
    SET @sSqlString = @sSqlString + ' and EL.EntryDate >= @1 and EL.EntryDate <= @2  GROUP BY U.UserID, U.UserName'
    END
ELSE 
    SET @sSqlString = 'SELECT U.UserID AS TotalHours, U.UserID, U.UserName From Users U Where 1=0'

EXEC sp_executesql @sSqlString, N'@1 datetime, @2 datetime, @3 int', @StartDate, @EndDate, @ManagerUserID

